package com.Util;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author :v-zhousq04
 * @description: 表操作类
 * @create date: 2018/9/3 13:53
 */
public class TabUtil {

    private static void setEntityJavaType(ColumnEntity entityVO, String dataType) {
        for (ColumnMapping columnMapping : ConstantCode.mappingList) {
            List<String> dbFieldTypeList = columnMapping.getDbFieldTypeList();
            if (dbFieldTypeList.indexOf(dataType) > -1) {
                entityVO.setJavaFieldType(columnMapping.getJavaType());
                entityVO.setJavaFieldTypeFull(columnMapping.getJavaTypeFull());
                entityVO.setColumnJdbcType(columnMapping.getColumnJdbcType());
            }
        }
    }

    /**
     * @param entityDataVO
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static void setTableInfo(EntityDataVO entityDataVO, String tableName) throws SQLException {
        String dataSourceUrl = entityDataVO.getDbConnectUrl();
        String dataBaseName = entityDataVO.getDefaultDBName();
        Connection conn = null;
        StringBuilder sqlBuild = new StringBuilder();
        List<ColumnEntity> entityObj = null;
        boolean isTrueDriverName = true;
        String driverName = "";
        if (entityDataVO.getDbType().equals(ConstantCode.DB_TYPE_SQLSERVER)) {
            driverName = ConstantCode.SQLSERVER_DRIVER_S1;
            sqlBuild.append("SELECT a.name 'COLUMN_NAME',");
            sqlBuild.append("isnull(e.text,'') 'COLUMN_DEFAULT',");
            sqlBuild.append("(case when a.isnullable=1 then 'YES'else 'NO' end) 'IS_NULLABLE',");
            sqlBuild.append("b.name 'DATA_TYPE',");
            sqlBuild.append("(case when (SELECT count(*) FROM sysobjects  ");
            sqlBuild.append("WHERE (name in (SELECT name FROM sysindexes  ");
            sqlBuild.append("WHERE (id = a.id) AND (indid in  ");
            sqlBuild.append("(SELECT indid FROM sysindexkeys  ");
            sqlBuild.append("WHERE (id = a.id) AND (colid in  ");
            sqlBuild.append("(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  ");
            sqlBuild.append("AND (xtype = 'PK'))>0 then 'PRI' else '' end) 'COLUMN_KEY',");
            sqlBuild.append("isnull(g.[value], ' ') 'COLUMN_COMMENT',");
            sqlBuild.append("(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'auto'else '' end) 'EXTRA'");
            sqlBuild.append("FROM  syscolumns a ");
            sqlBuild.append("left join systypes b on a.xtype=b.xusertype  ");
            sqlBuild.append("inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
            sqlBuild.append("left join syscomments e on a.cdefault=e.id  ");
            sqlBuild.append("left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id");
            sqlBuild.append("left join sys.extended_properties f on d.id=f.class and f.minor_id=0");
            sqlBuild.append("where b.name is not null and d.name='" + tableName + "'");
            sqlBuild.append("order by a.id,a.colorder");
        } else if (entityDataVO.getDbType().equals(ConstantCode.DB_TYPE_POSTGRESQL)) {
            driverName = ConstantCode.POSTGRESQL_DRIVER;
            sqlBuild.append("SELECT ");
            sqlBuild.append("\tcol.COLUMN_NAME,col.COLUMN_DEFAULT,");
            sqlBuild.append("\tcol.IS_NULLABLE,col.udt_name as DATA_TYPE,");
            sqlBuild.append("\ta2.COLUMN_KEY,a1.COMMENT,");
            sqlBuild.append("\tcase  when position('nextval' in col.COLUMN_DEFAULT)>0 then 'auto' else '' end as EXTRA");
            sqlBuild.append("\tFROM");
            sqlBuild.append("\tinformation_schema.COLUMNS col");
            sqlBuild.append("\tLEFT JOIN");
            sqlBuild.append("\t(SELECT");
            sqlBuild.append("\tcol_description ( attr.attrelid, attr.attnum ) AS COMMENT,");
            sqlBuild.append("\tattr.attname ");
            sqlBuild.append("\tFROM");
            sqlBuild.append("\tpg_class AS cls");
            sqlBuild.append("\tJOIN pg_attribute AS attr ON attr.attrelid = cls.oid ");
            sqlBuild.append("\tWHERE");
            sqlBuild.append("\tcls.relname = '" + tableName + "' ");
            sqlBuild.append("\tAND attr.attnum > 0)a1 on col.COLUMN_NAME=a1.attname");
            sqlBuild.append("\tLEFT JOIN");
            sqlBuild.append("\t(SELECT");
            sqlBuild.append("\tattr.attname AS colname,");
            sqlBuild.append("\t'PRI' AS COLUMN_KEY");
            sqlBuild.append("\tFROM");
            sqlBuild.append("\tpg_constraint pcon");
            sqlBuild.append("\tJOIN pg_class c ON pcon.conrelid = c.oid");
            sqlBuild.append("\tJOIN pg_attribute attr ON attr.attrelid = c.oid ");
            sqlBuild.append("\tAND attr.attnum = pcon.conkey [ 1 ]");
            sqlBuild.append("\tWHERE");
            sqlBuild.append("\tc.relname = '" + tableName + "')a2 on col.COLUMN_NAME=a2.colname");
            sqlBuild.append("\tWHERE");
            sqlBuild.append("\t TABLE_NAME ='" + tableName + "'");
        } else {
            sqlBuild.append("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, COLUMN_KEY, COLUMN_COMMENT ,EXTRA");
            sqlBuild.append("    FROM information_schema.COLUMNS WHERE table_schema ='" + dataBaseName + "' AND table_name = '" + tableName + "'");
            sqlBuild.append(" ORDER BY ORDINAL_POSITION");
            try {
                driverName = ConstantCode.MYSQL_DRIVER_S1;
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
                isTrueDriverName = false;
            }
        }

        try {
            if (!isTrueDriverName) {
                driverName = ConstantCode.MYSQL_DRIVER_S2;
            }

            Class.forName(driverName);
            conn = DriverManager.getConnection(dataSourceUrl);
            if (entityDataVO.getPrintsql()) {
                System.out.println("----------------------exec sql ---------------------- ");
                System.out.println(sqlBuild.toString());
                System.out.println("----------------------exec sql ---------------------- ");
            }
            PreparedStatement stmt = conn.prepareStatement(sqlBuild.toString());
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                entityObj = new ArrayList<ColumnEntity>();
                ColumnEntity entity;
                String colDefault, dataType, isPrimaryKey, fieldComment, fieldNameLow, colName, auto;
                while (rs.next()) {
                    entity = new ColumnEntity();
                    entity.setFieldQualifier(ConstantCode.QUALIFIER_PRIVATE);

                    //  列名
                    colName = rs.getString(1);
                    entity.setSqlField(colName);
                    //转换为驼峰命名，替换掉下划线，生成首字母小写列名
                    fieldNameLow = TabUtil.renameCol(colName);
                    entity.setFieldNameLow(fieldNameLow);
                    //转换为驼峰命名，替换掉下划线，生成首字母大写列名
                    entity.setFieldName(TabUtil.upperFirstFieldName(fieldNameLow));

                    //数据库字段默认值
                    colDefault = rs.getString(2);
                    entity.setSqlFieldDefault(colDefault != null ? colDefault : colDefault);

                    //是否为空
                    entity.setSqlFieldNullable(rs.getString(3));

                    //sql字段类型
                    dataType = rs.getString(4);
                    entity.setSqlFieldDataType(dataType);

                    //转换Java对应数据类型
                    setEntityJavaType(entity, dataType);

                    //是否主键
                    isPrimaryKey = rs.getString(5);
                    entity.setSqlFieldPrimaryKey(isPrimaryKey);

                    //额外属性，用来确认是否自增
                    auto = rs.getString(7);
                    entity.setExtra(auto);
                    if (auto.indexOf("auto") != -1) {
                        entityDataVO.setAutoKey(true);
                    }

                    if ((isPrimaryKey != null &&isPrimaryKey.equals("PRI"))||auto.indexOf("auto") != -1) {
                        entityDataVO.setPrimaryKey(colName);
                        entityDataVO.setPrimaryKeyType(dataType);
                        entityDataVO.setPrimaryKeyGet("get"+entity.getFieldName()+"()");
                        entityDataVO.setJavaKey(entity.getFieldNameLow());
                        entityDataVO.setJavaKeyType(entity.getJavaFieldType());
                        entityDataVO.setJavaKeyTypeFull(entity.getJavaFieldTypeFull());
                    }

                    //字段注释
                    fieldComment = rs.getString(6);
                    if (null == fieldComment || fieldComment.isEmpty()) {
                        entity.setSqlFieldComment("");
                    } else {
                        entity.setSqlFieldComment(fieldComment);
                    }

                    entityObj.add(entity);
                }
            }
        } catch (SQLException e) {
            System.out.println("数据库操作错误");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        entityDataVO.setColumnEntityList(entityObj);
    }

    public static Boolean valideParam(EntityDataVO entityDataVO) throws Exception {

        Boolean isValide = true;
        if ("".equals(entityDataVO.getAuthor())) {
            isValide = false;
            throw new Exception("author cannot be empty");
        }
        if ("".equals(entityDataVO.getDefaultDBName())) {
            isValide = false;
            throw new Exception("DataBaseName cannot be empty");
        }
        if ("".equals(entityDataVO.getTableName())) {
            isValide = false;
            throw new Exception("TableName cannot be empty");
        }
        if ("".equals(entityDataVO.getDbConnectUrl())) {
            isValide = false;
            throw new Exception("DataSourceUrl cannot be empty");
        }
        if ("".equals(entityDataVO.getDesc())) {
            isValide = false;
            throw new Exception("EntityDesc cannot be empty");
        }
        return isValide;
    }

    public static String renameCol(String tableName) {
        String tableNameArr[] = tableName.split("_");
        StringBuffer entityName = new StringBuffer();
        String s1;
        if (tableNameArr.length == 1) {
            entityName.append(tableNameArr[0]);
        } else {
            for (int i = 0; i < tableNameArr.length; i++) {
                String s = tableNameArr[i];
                if (i != 0) {
                    s1 = String.valueOf(s.charAt(0)).toUpperCase();
                    if (s.length() > 1) {
                        entityName.append(s1).append(s.substring(1));
                    } else {
                        entityName.append(s1);
                    }
                } else {
                    entityName.append(s);
                }
            }
        }
        return entityName.toString();
    }

    /**
     * 通过Freemaker模板生成文件
     *
     * @param baseData
     * @param templateFile
     * @param fileName
     * @param pkgPath
     * @throws IOException
     * @throws TemplateException
     */
    public static void generateFile(EntityDataVO baseData, String templateFile, String fileName, String pkgPath) throws IOException, TemplateException {
        // step1 创建freeMarker配置实例
        Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
        Writer out = null;
        // step2 获取模版路径
//        configuration.setDirectoryForTemplateLoading(new File(TEMPLATE_PATH));
//        configuration.setClassForTemplateLoading(TabUtil.class, "/template");
        configuration.setClassForTemplateLoading(TabUtil.class, GeneratorUtil.templatePath);
        Template template = configuration.getTemplate(templateFile);


        // step5 生成数据
//        pkgPath = pkgPath.replaceAll("\\.", "/");
        File docFile = new File(pkgPath + "\\" + fileName);
//        File docFile = new File(pkgPath + "\\" + fileName);

        //目录不存在时创建
        File folder = new File(pkgPath);
        if (!folder.exists()) {
            folder.mkdir();
        }
        out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
        // step6 输出文件
        template.process(baseData, out);
        System.out.println("********************文件:" + docFile + "创建成功 !");
    }

    /**
     * 转换首字母大写
     *
     * @param tableName
     * @return
     */
    public static String upperFirstFieldName(String tableName) {
        return String.valueOf(tableName.charAt(0)).toUpperCase() + tableName.substring(1);
    }
}
